package dao.mysql;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.logging.Level;
import java.util.logging.Logger;

import model.PositionDTO;
import model.UserDTO;
import exception.DAOSystemException;
import dao.PositionDAO;

public class PositionDAOImpl implements PositionDAO {
	MySqlImpl mysqlImpl = new MySqlImpl();

	@Override
	public PositionDTO findPosition(String id) throws DAOSystemException {
		// TODO Auto-generated method stub
		String selectSql = "SELECT * FROM position WHERE id='" + id.trim()
				+ "';";
		PositionDTO onePosition = new PositionDTO();
		try {
			ResultSet rs = mysqlImpl.get(selectSql);

			while (rs.next()) {
				onePosition.setId(rs.getString("id"));
				onePosition.setRank(rs.getString("rank"));
				onePosition.setRole(rs.getString("role"));
				onePosition.setTotalAnnualAllowance(rs
						.getDouble("totalAnnualAllowance"));
				onePosition.setTotalMedicalAllowance(rs.getDouble("totalMedicalAllowance"));
			}

		} catch (Exception e) {
			String error = "Error selecting position. Nested Exception is: " + e;
			Logger.getLogger(getClass().getName()).log(Level.SEVERE, error);
			throw new DAOSystemException(error);
		} finally {
			try {
				mysqlImpl.close();
			} catch (Exception e) {
			}
		}

		return onePosition;

	}

	@Override
	public ArrayList<PositionDTO> findAllPositions() throws DAOSystemException {
		// TODO Auto-generated method stub
		ArrayList<PositionDTO> items = new ArrayList<PositionDTO>();
		String selectSql = "SELECT * FROM position;";

		try {

			Logger.getLogger(getClass().getName()).log(Level.INFO,
					"Executing select: " + selectSql);
			ResultSet rs = mysqlImpl.get(selectSql);
			PositionDTO onePosition=null;

			while (rs.next()) {
				onePosition = new PositionDTO();
				onePosition.setId(rs.getString("id"));
				onePosition.setRank(rs.getString("rank"));
				onePosition.setRole(rs.getString("role"));
				onePosition.setTotalAnnualAllowance(rs
						.getDouble("totalAnnualAllowance"));
				onePosition.setTotalMedicalAllowance(rs.getDouble("totalMedicalAllowance"));
				items.add(onePosition);
			}

		} catch (Exception e) {
			String error = "Error selecting position. Nested Exception is: " + e;
			Logger.getLogger(getClass().getName()).log(Level.SEVERE, error);
			throw new DAOSystemException(error);
		} finally {
			try {
				mysqlImpl.close();
			} catch (Exception e) {
			}
		}
		return items;
	}

	@Override
	public void insertPosition(PositionDTO position) throws DAOSystemException {
		// TODO Auto-generated method stub
		String insertSql = "INSERT INTO position VALUES('" + position.getId()
				+ "', '" + position.getRole() + "', '" + position.getRank()
				+ "', '" + position.getTotalAnnualAllowance() + "', '"
				+ position.getTotalMedicalAllowance() + "');";

		try {

			Logger.getLogger(getClass().getName()).log(Level.INFO,
					"Executing insert: " + insertSql);
			int res = mysqlImpl.execute(insertSql);

		} catch (Exception e) {
			String error = "Error selecting positioon. Nested Exception is: " + e;
			Logger.getLogger(getClass().getName()).log(Level.SEVERE, error);
			throw new DAOSystemException(error);
		} finally {
			try {
				mysqlImpl.close();
			} catch (Exception e) {
			}
		}

	}

	@Override
	public void updatePosition(PositionDTO position) throws DAOSystemException {
		// TODO Auto-generated method stub
		String updateSql = "UPDATE position SET role = '" + position.getRole()
				+ "', rank = '" + position.getRank()
				+ "', totalAnnualAllowance = '"
				+ position.getTotalAnnualAllowance()
				+ "', totalMedicalAllowance = '"
				+ position.getTotalMedicalAllowance()+ "' WHERE id='" + position.getId()
				+ "';";

		try {

			Logger.getLogger(getClass().getName()).log(Level.INFO,
					"Executing update: " + updateSql);
			int res = mysqlImpl.execute(updateSql);

		} catch (Exception e) {
			String error = "Error selecting position. Nested Exception is: "
					+ e;
			Logger.getLogger(getClass().getName()).log(Level.SEVERE, error);
			throw new DAOSystemException(error);
		} finally {
			try {
				mysqlImpl.close();
			} catch (Exception e) {
			}
		}

	}

	@Override
	public void deletePosition(PositionDTO position) throws DAOSystemException {
		// TODO Auto-generated method stub
		String deleteSql = "DELETE FROM position WHERE id='"
				+ position.getId().trim() + "';";

		try {
			mysqlImpl.execute(deleteSql);
		} catch (SQLException e) {
			String error = "Error deleting position. Nested Exception is: " + e;
			Logger.getLogger(getClass().getName()).log(Level.SEVERE, error);
			try {
				mysqlImpl.rollBack();
			} catch (SQLException e1) {
				// TODO Auto-generated catch block
				e1.printStackTrace();
			}
			throw new DAOSystemException(error);
		} finally {
			try {
				mysqlImpl.close();
			} catch (Exception e) {
			}
		}

	}

}
